package DBLayer;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.Product;
import ModelLayer.Supplier;


public class DBProduct implements IFDBProduct 
{
    private  Connection con;
    
    public DBProduct() 
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    @Override
    public Product findProduct(int productID, boolean retriveAssociation)
    {
        Product protObj = new Product();
        protObj = singleWhere("pId = " + productID, retriveAssociation);
        
        return protObj;
    }

        @Override
    public Product searchProductSup(int suId, boolean retriveAssociation) 
    {
        String wClause = "Select suId from Product Where pId like '%" + retriveAssociation + "%'";
        
        return singleWhere(wClause, retriveAssociation);
    }
    
    
    @Override
    public ArrayList<Product> getAllProducts(boolean retriveAssociation) 
    {
       ArrayList list = new ArrayList();
        list = miscWhere("", retriveAssociation);
        return list;
    }

    
    
    @Override
    public Product searchProductOnName(String attValue, boolean retriveAssociation) 
    {
        String wClause = "name like '%" + attValue + "%'";
        
        return singleWhere(wClause, retriveAssociation);
    }

    @Override
    public Product searchProductType(String attValue, boolean retriveAssociation) 
    {
        String wClause = "type like '%" + attValue + "%'";
        
        return singleWhere(wClause, retriveAssociation);
    }

    @Override
    public Product searchProductID(int attValue, boolean retriveAssociation) 
    {
        String wClause = "pId like '%" + attValue + "%'";

        return singleWhere(wClause, retriveAssociation);
    }

    private Product singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Product proObj = new Product();
        String query =  buildQuery(wClause);

        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next())
            {
                proObj = buildProduct(results);
            }

            stmt.close();
        }
        catch(Exception e)
        {

        }
        
        return proObj;
    }
    
    private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Product> list = new ArrayList<Product>();

        String query =  buildQuery(wClause);

        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while( results.next() )
            {
                Product proObj = new Product();
                proObj = buildProduct(results);	
                list.add(proObj);
            }

            stmt.close();
        }	
        catch(Exception e)
        {

        }
        
        return list;
    }
    
    private Product buildProduct(ResultSet results)
    {   
        Product productObj = new Product();
        
        try
        { 
            productObj.setProductID(results.getInt("pId"));
            productObj.setSupplierID(new Supplier(results.getInt("suId")));
            productObj.setType(results.getString("type"));
            productObj.setPrice(results.getDouble("price"));
            productObj.setDimensions(results.getDouble("dimensions"));
            productObj.setPlacement(results.getString("placement"));
            productObj.setMinStock(results.getInt("minStock"));
            productObj.setMaxStock(results.getInt("maxStock"));
            productObj.setCurrentStock(results.getInt("currentStock"));
        }
        catch(Exception e){}

        return productObj;
    }

    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Product";

        if (wClause.length()>0)
        {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    @Override
    public void insertProduct(Product pro)
    {  
        String query="INSERT INTO Product(suId, type, price, dimensions, placement, minStock, maxStock, currentStock)"  
                + "VALUES("+
                "" + pro.getSupplierID().getSupID() + ", " + 
               "'"+ pro.getType()  + "', " +
               " "+ pro.getPrice()  + ", "  +
               " "+ pro.getDimensions()  + ", "  +
               "'"+ pro.getPlacement()  + "',"  +
               " "+ pro.getMinStock()  + ", "  +
               " "+ pro.getMaxStock()  + ", "  +
               " "+ pro.getCurrentStock()  + ") ";
     
        System.out.println("Insert query: " + query);
         try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            
            
            stmt.close();
        }
        catch(Exception e)
        {
            
        }
    }

    @Override
    public void updateProduct (Product pro)
    {
        Product proObj  = pro;
   String query=
                "UPDATE Product SET "+
                "type ='"+ proObj.getType()+"', "+
                "price ='"+ proObj.getPrice() + "', " +
                "dimensions ='"+ proObj.getDimensions() + "', " +
                "placement ='"+ proObj.getPlacement() + "', " +
                "minStock ='"+ proObj.getMinStock() + "', " +
                "maxStock ='"+ proObj.getMaxStock() + "', " +
                "currentStock ='"+ proObj.getCurrentStock() + "' " +
             // "suId ='"+ proObj.getSupplier().getSupID() + "' " +
                "WHERE pId = '"+ proObj.getProductID() + "'";
      
        System.out.println("Update query: " + query);
         try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception ex)
        {
        }            
    }
    
    @Override
    public void updateProductStock(int productID, int newStock)
    {
        String query="UPDATE Product SET "+
                "currentStock ='"+ newStock + "' " +
                " WHERE pId = '"+ productID + "'";
      
          System.out.println("Update query: " + query);
         try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            stmt.close();
        }
        catch(Exception ex)
        {

        }            
    }
	
    @Override
    public void deleteProduct(int productID)
    {
        String query="DELETE FROM Product WHERE pId = '" +
                        productID + "'";

        try
        { 
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                 stmt.executeUpdate(query);
                stmt.close();
        }
        catch(Exception ex)
        {

        }
    }

   @Override
    public int MaxID()
    {
        String query = "SELECT max(pId) FROM Product";
        ResultSet results;
        int id = -1;
        try
        {
            Statement stmt = con.createStatement();
            results = stmt.executeQuery(query);
            if( results.next())
            {
                id = results.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println("Query exception: Error in reading max id: " + e);
        } 
        return id;     
    }

    @Override
    public int Antal() {
String query = "SELECT COUNT(*) FROM Product";
        ResultSet results;
        int id = -1;
        try
        {
            Statement stmt = con.createStatement();
            results = stmt.executeQuery(query);
            if( results.next())
            {
                id = results.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println("Query exception: Error in reading count: " + e);
        } 
        return id;   
    }

} 